package group2assignone.dao;

import group2assignone.model.Airport;
import group2assignone.model.Customer;
import group2assignone.model.Flight;
import group2assignone.model.Manager;
import group2assignone.model.Person;
import group2assignone.model.TicketComponent;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Group 2
 *
 * Class MySqlComms:
 * A Singleton class which handles all communications to the MySQL database.
 * connects with
 *  userName = "3120S109g02";
 * password = "cc03086abb";
 * and the  url = "jdbc:mysql://dunk.it.jcu.edu.au:3306/3120S109g02";
 *
 * within this class persons, flights and bookings can be added, deleted, edited and found.
 */
public class PhpAdminSQLDAO implements MyDAO {

    private static Connection conn = null;
    private static PhpAdminSQLDAO instance;

    private PhpAdminSQLDAO() {

        try {
            System.out.println("trying to connect to phpmyadmin");
            String userName = "3120S109g02";
            String password = "cc03086abb";
            String url = "jdbc:mysql://dunk.it.jcu.edu.au:3306/3120S109g02";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);

            System.out.println("Database connection established");

        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
        }
    }

    public static PhpAdminSQLDAO getInstance() {
        if (instance == null) {
            instance = new PhpAdminSQLDAO();
        }
        return instance;
    }


//------------------------------------------------------------------------------

//------------------------------------------------------------------------------
//------------------------------------------------------------------------------
    /**
     * public boolean sQLAddCustomer(Customer c)
     * this function adds a new customer to the database tabel called "Customer"
     * it recieves a paramater customer and returns a boolean reult to notify if it wass added correctly
     * @param c
     * customer paramater input
     * @return
     * boolean result if worked or not
     * return true for worked
     * return false for failed
     */
    public boolean sQLAddCustomer(Customer c) {
        boolean result = sQLAddPerson(c, "Customer");
        return result;
    }

    /**
     * public boolean sQLAddManager(Manager m)
     * this function adds a new manager to the database tabel called "Manager"
     * it recieves a paramater customer and returns a boolean reult to notify if it wass added correctly
     * @param m
     * Manager paramater input
     * @return
     * boolean result if worked or not
     * return true for worked
     * return false for failed
     */
    public boolean sQLAddManager(Manager m) {
        boolean result = sQLAddPerson(m, "Manager");
        return result;
    }

    private boolean sQLAddPerson(Person prsn, String tableName) {
        boolean worked;


        try {
            Statement s = conn.createStatement();


            s.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " (username VARCHAR(8), password VARCHAR(8), firstName VARCHAR(10), lastName VARCHAR(10), DOB VARCHAR(10))");

            s.executeUpdate("INSERT INTO " + tableName + " (username, password, firstName, lastName, DOB) VALUES('" + prsn.getUsername() + "', '" + prsn.getPassword() + "', '" + prsn.getFName() + "', '" + prsn.getLName() + "', '" + prsn.getDob() + "')");
            //s.executeUpdate("INSERT INTO Manager (username, password, firstName, lastName, DOB) VALUES('123456789', '123456789', '123456789', '123456789', '123456789')");
            worked =
                    true;

        } catch (Exception e) {
            worked = false;
            System.err.println("Failed to add person: " + prsn.getUsername() + " to Table: " + tableName);

        }

        return worked;
    }


//------------------------------------------------------------------------------
    /**
     * public boolean sQLDeleteCustomer(String userName)
     * This function deletes a customer from the database table "Customer"
     * it receives a String user input 'userName'  and deletes the user with that matching paramater
     * @param userName
     * @return
     * returns a boolean result
     * true if deleted
     * false if failed to delete
     */
    public boolean sQLDeleteCustomer(String userName) {
        boolean result = sQLDeletePerson(userName, "Customer");
        return result;
    }

    /**
     * public boolean sQLDeleteManager(String userName)
     * This function deletes a customer from the database table "Manager"
     * it receives a String user input 'userName'  and deletes the user with that matching paramater
     * @param userName
     * @return
     * returns a boolean result
     * true if deleted
     * false if failed to delete
     */
    public boolean sQLDeleteManager(String userName) {
        boolean result = sQLDeletePerson(userName, "Manager");
        return result;
    }

    private boolean sQLDeletePerson(String username, String tableName) {
        boolean worked;
        System.out.println("in delete person");

        try {
            Statement s = conn.createStatement();
            s.executeUpdate("DELETE FROM " + tableName +
                    " WHERE username = '" + username + "'");
            worked =
                    true;
        } catch (Exception e) {
            worked = false;
            System.err.println("Failed to delete person: " + username + " from Table: " + tableName);

        }

        return worked;
    }


//------------------------------------------------------------------------------
    /**
     * This function, public ArrayList<Manager> sQLGetAllFromManager()
     * searches and returns all existing managers from the table manager
     * @return
     * an arrayList of Manager Objects
     */
    public ArrayList<Manager> sQLGetAllFromManager() {
        ArrayList<Manager> manList = new ArrayList<Manager>();
        Manager man;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM Manager");
            System.out.println("got manager table");
            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                man = new Manager();
                String username = rs.getString("username");
                String password = rs.getString("password");
                String firstName = rs.getString("firstName");
                String lastName = rs.getString("lastName");
                String dOB = rs.getString("DOB");

                man.setUsername(username);
                man.setPassword(password);
                man.setFName(firstName);
                man.setLName(lastName);
                man.setDob(dOB);

                manList.add(man);

                // System.out.println(
                //         " username = " + username + ", password = " + password + ", firstName = " + firstName + ", lastName = " + lastName + ", DOB = " + dOB);
                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            manList.clear();
            manList =
                    null;
            System.out.println("Exeption in get all from manager command");


        }

        return manList;
    }
//------------------------------------------------------------------------------

    /**
     * public boolean sQLEditCustomer(Customer c, String uNameOld)
     * this function edits an existing customer
     * it recieves an object customer, which contains the updated data, and the old usernumber
     * this is to keep track of the existing if the username was changed
     * @param c
     * Customer object recieved with edited data
     * @param uNameOld
     * the old username, this is used to find the object to be updated
     * @return
     * returns a boolean result:
     * true = edites ok
     * false = edit failed
     */
    public boolean sQLEditCustomer(Customer c, String uNameOld) {
        boolean result = sQLEditPerson((Person) c, "Customer", uNameOld);
        return result;
    }

    /**
     *  public boolean sQLEditManager(Manager m, String uNameOld)
     * this function edits an existing Manager
     * it recieves an object customer, which contains the updated data, and the old usernumber
     * this is to keep track of the existing if the username was changed
     * @param m
     * Manager object recieved with edited data
     * @param uNameOld
     * the old username, this is used to find the object to be updated
     * @return
     * returns a boolean result:
     * true = edites ok
     * false = edit failed
     */
    public boolean sQLEditManager(Manager m, String uNameOld) {
        boolean result = sQLEditPerson((Person) m, "Manager", uNameOld);
        return result;
    }

    private boolean sQLEditPerson(Person prsn, String tableName, String uNameOld) {
        boolean worked;

        try {
            System.out.println("in edit person");
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeUpdate("UPDATE " + tableName + " " +
                    "SET username = '" + prsn.getUsername() + "', password = '" + prsn.getPassword() +
                    "', firstName = '" + prsn.getFName() + "', lastName = '" + prsn.getLName() +
                    "', DOB = '" + prsn.getDob() + "'" +
                    "WHERE username = '" + uNameOld + "'");

            worked =
                    true;

        } catch (Exception e) {
            worked = false;
            System.err.println("exception!!!!");
        }

        return worked;
    }


//--------------------------------------------
//**************************************************************************
    /**
     * public Customer sQLGetCustomer(String uName)
     * This function attempts to retrieve a Cusomer from the customer table
     * the object customer is returned.
     * @param uName
     * the username is received for the object to find
     * @return
     * retunrs the object found
     */
    public Customer sQLGetCustomer(String uName) {
        Customer cust = (Customer) sQLGetPerson("Customer", uName);
        return cust;
    }

    /**
     * public Manager sQLGetManager(String uName)
     * This function attempts to retrieve a Manager from the manager table
     * the object customer is returned.
     * @param uName
     * the username is received for the object to find
     * @return
     * retunrs the object found
     */
    public Manager sQLGetManager(String uName) {
        Manager man = (Manager) sQLGetPerson("Manager", uName);
        return man;
    }

    private Person sQLGetPerson(String tableName, String uName) {
        Person prsn = new Person();

        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeQuery("SELECT * FROM " + tableName + " " +
                    "WHERE username = '" + uName + "'");

            ResultSet rs = s.getResultSet();
            if (rs.next()) {
                prsn.setUsername(rs.getString("username"));
                prsn.setPassword(rs.getString("password"));
                prsn.setFName(rs.getString("firstName"));
                prsn.setLName(rs.getString("lastName"));
                prsn.setDob(rs.getString("DOB"));
            }

        } catch (Exception e) {
            prsn = null;
            System.err.println("Failed to get person: " + uName + " from Table: " + tableName);
        }

        return prsn;
    }


//------------------------------------------------------------------------------
//--------------------------------------------
    /**
     * public Person sQLFindPerson(String uName)
     * This function searches through all tables for a given username
     * it returns an object person if there is a match
     * @param uName
     * the recieved username to find
     * @return
     * returns an object of person if it was found
     */
    public Person sQLFindPerson(String uName) {
        Person prsn = new Person();

        try {
            Statement s = conn.createStatement();

            s.executeQuery("SELECT userName FROM *" +
                    "WHERE username = '" + uName + "'");

            ResultSet rs = s.getResultSet();
            if (rs != null) {
                prsn.setUsername(rs.getString("username"));
                prsn.setPassword(rs.getString("password"));
                prsn.setFName(rs.getString("firstName"));
                prsn.setLName(rs.getString("lastName"));
                prsn.setDob(rs.getString("DOB"));
            } else {
                prsn = null;
            }

        } catch (Exception e) {
            prsn = null;
            System.err.println("Failed to find person: " + uName);
        }

        return prsn;
    }

//------------------------------------------------------------------------------
    /**
     * public void sQLCreateFlight(Flight flt)
     * this function adds a new flight to the database
     * it uses all info saved to the paramater Flight recieved
     * @param flt
     * this recieved paramater is an object of Flight. it contains all
     * data for the new flight
     * @return
     * returns the boolean result.
     * true if flight created
     * false if failed to add new flight
     *
     */
    public boolean sQLCreateFlight(Flight flt) {
        boolean result = false;


        try {
            Statement s = conn.createStatement();
            s.executeUpdate("CREATE TABLE IF NOT EXISTS Flight (flightNum VARCHAR(4), depLoc text, depTime VARCHAR(7), arrLoc text, arrTime VARCHAR(7), seatCap int(3), flightDate VARCHAR(10), price VARCHAR(4), promotion int(3))");

            s.executeUpdate("INSERT INTO Flight (flightNum, depLoc, depTime, arrLoc, arrTime, seatCap, flightDate, price, promotion) VALUES('" + flt.getFlightNum() + "', '" + flt.getDepLoc() + "', '" + flt.getDepTime() + "', '" + flt.getArrLoc() + "', '" + flt.getArrTime() + "', '" + flt.getSeatCap() + "', '" + flt.getDate() + "', '" + flt.getCost() + "', '" + flt.getPromotion() + "')");
            result =
                    true;

        } catch (Exception e) {
            result = false;
            System.err.println("Failed to add flight: " + flt.getFlightNum());

        }

        return result;

    }

//-*************************************************************************
    /**
     * public void sQLEditFlight(Flight flt, String flightNumOld)
     * this function edits an existing flight. it recieves an object of the new
     * Edited flight and a String containing the old flight num, in case it was changed
     * @param flt
     * this recieved paramater contains the detais of the edited flight
     * @param flightNumOld
     * this recieved string holds the old flight number so the system can find it in the table to edit
     * @return
     * returns a boolean result
     * true = edit flight ok
     * false = failed to edit flight
     */
    public boolean sQLEditFlight(Flight flt, String flightNumOld) {
        boolean result = false;
        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeUpdate("UPDATE Flight " +
                    "SET flightNum = '" + flt.getFlightNum() + "', depLoc = '" + flt.getDepLoc() +
                    "', depTime = '" + flt.getDepTime() + "', arrLoc = '" + flt.getArrLoc() +
                    "', arrTime = '" + flt.getArrTime() + "', seatCap = '" + flt.getSeatCap() +
                    "', flightDate = '" + flt.getDate() + "', price = '" + flt.getCost() + "'" +
                    "', promotion = '" + flt.getPromotion() +
                    "WHERE flightNum = '" + flightNumOld + "'");
            result =
                    true;
        } catch (Exception e) {
            System.err.println("Failed to Edit Flight");
            result =
                    false;
        }

        return result;
    }

//--------------------------------------------------------------------------
    /**
     *  public void sQLDeleteFlight(String flightNum)
     * this function deletes a selected flight. it recieves the corresponding
     * flight number, searches for it in the table and deletes it if it finds it
     * @param flightNum
     * this recieved flight number string gives the ID for the flight to be deleted
     * the flight is deleted if it is found
     *
     * @return
     * retuns a boolean result
     * true = flight deleted
     * false = failed to delete, likely to be from conenction error
     */
    public boolean sQLDeleteFlight(String flightNum) {
        boolean result = false;
        System.out.println("in delete Flight");

        try {
            Statement s = conn.createStatement();
            s.executeUpdate("DELETE FROM Flight" +
                    " WHERE flightNum = '" + flightNum + "'");
            result =
                    true;
        } catch (Exception e) {
            result = false;
            System.err.println("Failed to delete Flight: " + flightNum);

        }

        return result;
    }

//--------------------------------------------------------------------------
    /**
     * public ArrayList<Flight> sQLSearchFlights(Flight flt)
     * this function is used to find and return an arraylist of flights with similar
     * data. the function recieves an object of Flight containing departure location,
     * arrival location and date. it then searches the flight databse and returns all flights where these three
     * paramaters match
     * @param flt
     * this is the recieved paramater Flight object with the data to be matched
     * @return
     * retuns a arrayList of flights which match.
     * returns null if no matches
     */
    public ArrayList<Flight> sQLSearchFlights(Flight flt) {
        ArrayList<Flight> flightList = new ArrayList<Flight>();
        Flight flight;

        try {
            Statement s = conn.createStatement();
            s.executeQuery("SELECT * FROM Flight WHERE depLoc = '" + flt.getDepLoc() + "' AND arrLoc = '" + flt.getArrLoc() + "' AND flightDate = '" + flt.getDate() + "'");

            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                flight = new Flight();
                String flightNum = rs.getString("flightNum");
                String depLoc = rs.getString("depLoc");
                String depTime = rs.getString("depTime");
                String arrLoc = rs.getString("arrLoc");
                String arrTime = rs.getString("arrTime");
                int seatCap = rs.getInt("seatCap");
                String date = rs.getString("flightDate");
                String price = rs.getString("price");
                int promotion = rs.getInt("promotion");

                flight.setFlightNum(flightNum);
                flight.setDepLoc(depLoc);
                flight.setDepTime(depTime);
                flight.setArrLoc(arrLoc);
                flight.setArrTime(arrTime);
                flight.setSeatCap(seatCap);
                flight.setDate(date);
                flight.setCost(price);
                flight.setPromotion(promotion);

                flightList.add(flight);

                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {

            flightList = null;
            System.err.println("Unable to retrieve all flights with that data");
        }

        return flightList;
    }

//--------------------------------------------------------------------------
    /**
     * public boolean sQLCustomerExists(Customer cust)
     * this function checks if a particular customer exists in the database
     * this function may be used for authentication
     *
     * @param cust
     * paramater cust is an object of Customer who's username and password are
     * compared to against the database.
     * @return
     * returns a boolean result detemining if it exists in the database
     * true = username and password correct -> customer is valid
     * false = username or password do not match
     */
    public boolean sQLCustomerExists(Customer cust) {

        return sQLPersonExists("Customer", (Person) cust);
    }

    /**
     * public boolean sQLManagerExists(Manager man)
     * this function checks if a particular manager exists in the database
     * this function may be used for authentication
     *
     * @param man
     * paramater man is an object of Manager who's username and password are
     * compared to against the database.
     * @return
     * returns a boolean result detemining if it exists in the database
     * true = username and password correct -> manager is valid
     * false = username or password do not match
     */
    public boolean sQLManagerExists(Manager man) {

        return sQLPersonExists("Manager", (Person) man);
    }
//**************************************************************************

    private boolean sQLPersonExists(String tableName, Person psn) {
        boolean result = false;


        try {
            Statement s = conn.createStatement();

            s.executeQuery("SELECT * FROM " + tableName +
                    " WHERE username = '" + psn.getUsername() + "'");

            ResultSet rs = s.getResultSet();
            if (rs != null) {
                rs.next();
                if (rs.getString("username").equals(psn.getUsername())) {
                    System.out.println("Correct username found in sQLPersonExists");
                    if (rs.getString("password").equals(psn.getPassword())) {
                        System.out.println("Correct password found in sQLPersonExists");
                        result =
                                true;
                    }

                }
            }
        } catch (Exception e) {
            result = false;
            System.err.println("Failed to search for person");
        }

        return result;
    }

    public Flight sQLGetFlight(String fnum) {
        Flight flight = new Flight();

        try {

            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed

            s.executeQuery("SELECT * FROM Flight " +
                    "WHERE flightNum = '" + fnum + "'");

            ResultSet rs = s.getResultSet();

            if (rs.next()) {
                System.out.println(rs.getString("flightNum"));
                flight.setFlightNum(rs.getString("flightNum"));
                flight.setDepLoc(rs.getString("depLoc"));
                flight.setDepTime(rs.getString("depTime"));
                flight.setArrLoc(rs.getString("arrLoc"));
                flight.setArrTime(rs.getString("arrTime"));
                flight.setSeatCap(rs.getInt("seatCap"));
                flight.setDate(rs.getString("flightDate"));
                flight.setCost(rs.getString("price"));
                flight.setPromotion(rs.getInt("promotion"));
            }
        } catch (Exception e) {
            flight = null;
            System.err.println("Failed to get flight: " + fnum + " from Table: Flight");
        }
        return flight;
    }

    //------------------------------------------------------------------------------
    /**
     * This function, public ArrayList<Flight> getFlights()
     * searches and returns all existing flights from the table Flight
     * @return
     * an arrayList of flight Objects
     */
    public ArrayList<Flight> getFlights() {
        ArrayList<Flight> flightList = new ArrayList<Flight>();
        Flight flt;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM Flight");
            System.out.println("got flight table");
            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                flt = new Flight();
                flt.setFlightNum(rs.getString("flightNum"));
                flt.setDepLoc(rs.getString("depLoc"));
                flt.setDepTime(rs.getString("depTime"));
                flt.setArrLoc(rs.getString("arrLoc"));
                flt.setArrTime(rs.getString("arrTime"));
                flt.setSeatCap(rs.getInt("seatCap"));
                flt.setDate(rs.getString("flightDate"));
                flt.setCost(rs.getString("price"));
                flt.setPromotion(rs.getInt("promotion"));

                flightList.add(flt);

                // System.out.println(
                //         " username = " + username + ", password = " + password + ", firstName = " + firstName + ", lastName = " + lastName + ", DOB = " + dOB);
                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            flightList.clear();
            flightList = null;
            System.out.println("Exeption in get all from flight command");


        }

        return flightList;
    }
//------------------------------------------------------------------------------
public boolean sQLMakeBooking(TicketComponent ticket, String bookingNum)
    {
        //String bookingNum = booking.getBookingNum();
        System.out.println("Booking being made");

        return true;
    }

    public boolean sQLAddAirport(Airport airport) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<Airport> sQLGetAirports() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean sQLEditAirport(Airport ap, String airportLocOld) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean sQLDeleteAirport(String airportLoc) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

        public boolean sQLAddPromotion(Flight flt) {
        boolean result = false;
        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeUpdate("UPDATE Flight " +
                    "SET promotion = '" + flt.getPromotion() + "'WHERE flightNum = '" + flt.getFlightNum() + "'");
            result =
                    true;
            System.out.println("adding promotion of " + flt.getPromotion());
            System.out.println("on dunk server");
        } catch (Exception e) {
            System.err.println("Failed to Edit Flight");
            result =
                    false;
        }

        return result;
    }
}
